import openpyxl,os
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill

def loadBill(file):
    bill = {}
    wb = openpyxl.load_workbook(file)
    sheet = wb.get_sheet_by_name('按量计费')
    for row in range(3, sheet.max_row+1):
        item = sheet['D' + str(row)].value
        cost = sheet['I' + str(row)].value
        bill.setdefault(item, 0)
        bill[item] += cost
    return bill

def compare(old, new):
    result = {}
    for newKey in new.keys():
        newValue = new.get(newKey)
        oldValue = old.get(newKey, 0)
        result[newKey] = newValue - oldValue
    for oldKey in old.keys():
        if oldKey not in new.keys():
            newValue = new.get(newKey, 0)
            oldValue = old.get(newKey, 0)
            result[newKey] = newValue - oldValue
    return result

def outputData(src_file_name1, src_file_name2, src_data1, src_data2, diff_data):
    wb = openpyxl.Workbook()
    sheet = wb.worksheets[0]
    # 标题行
    row = 1
    sheet['A' + str(row)].value = "服务目录"
    sheet.column_dimensions['A'].width = 40
    sheet['B' + str(row)].value = src_file_name1
    sheet.column_dimensions['B'].width = 30
    sheet['C' + str(row)].value = src_file_name2
    sheet.column_dimensions['C'].width = 30
    sheet['D' + str(row)].value = "差额"
    # 数据行
    for key in diff_data.keys():
        row = row + 1
        sheet['A' + str(row)].value = key
        if key in src_data1.keys():
            sheet['B' + str(row)].value = src_data1[key]
        else:
            sheet['B' + str(row)].value = 0
        sheet['C' + str(row)].value = src_data2[key]
        sheet['D' + str(row)].value = diff_data[key]
        # 触发需要提醒的行数据
        if diff_data[key] > 5000:
            sheet['D' + str(row)].fill = PatternFill(fill_type='solid', start_color='FFFF00', end_color='000000')
    # 汇总行
    row+=1
    sheet['A' + str(row)].value = "合计"
    sheet['B' + str(row)].value = "=SUM(B2:B"+str(row-1)+")"
    sheet['C' + str(row)].value = "=SUM(C2:C"+str(row-1)+")"
    sheet['D' + str(row)].value = "=C"+str(row)+"-B"+str(row)
    # 统一格式化单元格
    max_column_letter = openpyxl.utils.get_column_letter(sheet.max_column)
    for sheetRow in sheet['A1':max_column_letter+str(sheet.max_row)]:
        for cell in sheetRow:
            cellFormat(cell)
    wb.save('SLA账单比对.xlsx')

# 格式化单元格
def cellFormat(cell):
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.border = Border(left=Side(border_style='thin', color='000000'), right=Side(border_style='thin', color='000000'),
                    top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000'))


if __name__ == '__main__':

    # 招商平安资产
    src_path1 = "/Users/zhangxiangbin/GitRepos/amc-doc/13SLA账单/2020年/深圳市招商平安资产管理有限责任公司_2020年1-6月_正式账单.xlsx"
    src_path2 = "/Users/zhangxiangbin/GitRepos/amc-doc/13SLA账单/2021年/深圳市招商平安资产管理有限责任公司_2021年1-6月_计提账单.xlsx"

    # 招商局资本
    # src_path1 = "/Users/zhangxiangbin/Downloads/招商局资本管理有限责任公司_2020年7-12月_正式账单.xlsx"
    # src_path2 = "/Users/zhangxiangbin/Downloads/招商局资本管理有限责任公司_2021年1-6月_计提账单.xlsx"

    # 服贸中垦
    # src_path1 = "/Users/zhangxiangbin/GitRepos/fmcs-doc/100服贸农垦/SLA/2020年/账单/招商局资本管理（北京）有限公司_2020年10-12月_正式账单.xlsx"
    # src_path2 = "/Users/zhangxiangbin/GitRepos/fmcs-doc/100服贸农垦/SLA/2021年/招垦资本管理（北京）有限公司_2021年4-6月_计提账单.xlsx"

    src_data1 = loadBill(src_path1)
    src_data2 = loadBill(src_path2)
    diff_data = compare(src_data1, src_data2)
    src_file_name1 = os.path.basename(src_path1)
    src_file_name2 = os.path.basename(src_path2)
    outputData(src_file_name1, src_file_name2, src_data1, src_data2, diff_data)
